package com.surprise.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.surprise.base.bo.PageReq;
import com.surprise.base.bo.QueryCondition;
import com.surprise.base.util.SQLUtils;
import com.surprise.db.po.Spot;

/**
 * SpotDAO.java
 * @author nick
 * @since 2015年5月8日
 */
@Repository
public class SpotDAO extends SimpleDAO<Spot> {
	public final String columns = "(id,category,title,abbrContent,content,img,starttime,endtime,author,mallid,shopid,cityid,status,smallImg,joinAddress,starNum,couponid,videoHtml,userid,addtime)";
	public final String insertStatement = "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now())";
	public final String ON_DUPLICATE_KEY = " on duplicate key update title = values(title), content = values(content), img = values(img)"
			+ " mallid = values(mallid), shopid = values(shopid), cityid = values(cityid),"
			+ " starttime = values(starttime), endtime = values(endtime), author=values(author)";
	
	public static class SpotSetter implements BatchPreparedStatementSetter {
		private List<Spot> items;
		
		public SpotSetter(List<Spot> items) {
			this.items = items;
		}
		
		@Override
		public void setValues(PreparedStatement ps, int i) throws SQLException {
			Spot item = items.get(i);
			int index = 1;
			ps.setLong(index++, item.getId());
			ps.setInt(index++, item.getCategory());
			ps.setString(index++, item.getTitle());
			ps.setString(index++, item.getAbbrContent());
			ps.setString(index++, item.getContent());
			ps.setString(index++, item.getImg());
			ps.setLong(index++, item.getStarttime());
			ps.setLong(index++, item.getEndtime());
			ps.setString(index++, item.getAuthor());
			ps.setLong(index++, item.getMallid());
			ps.setLong(index++, item.getShopid());
			ps.setLong(index++, item.getCityid());
			ps.setInt(index++, item.getStatus());
			ps.setString(index++, item.getSmallImg());
			ps.setString(index++, item.getJoinAddress());
			ps.setInt(index++, item.getStarNum());
			ps.setLong(index++, item.getCouponid());
			ps.setString(index++, item.getVideoHtml());
			ps.setLong(index++, item.getUserid());
		}

		@Override
		public int getBatchSize() {
			return items.size();
		}		
	}
	
	public void add(List<Spot> items) {
		StringBuilder sb = new StringBuilder();
		sb.append("insert into ")
			.append(getTableName())
			.append(columns)
			.append(" values ")
			.append(insertStatement);
		String sql = sb.toString();
		jdbcTemplate.batchUpdate(sql, new SpotSetter(items));
	}
	
	public void edit(List<Spot> items) {
		StringBuilder sb = new StringBuilder();
		sb.append("insert into ")
			.append(getTableName())
			.append(columns)
			.append(" values ")
			.append(insertStatement)
			.append(ON_DUPLICATE_KEY);
		String sql = sb.toString();
		jdbcTemplate.batchUpdate(sql, new SpotSetter(items));
	}
	
	public List<Spot> findPriority() {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ").append(getTableName()).append(
                " where status = 0 and priority > 0 order by priority desc, updatetime desc");
        String sql = sb.toString();
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Spot>(Spot.class));
    }
	
	public List<Spot> page(PageReq page, QueryCondition condition) {
		StringBuilder sb = new StringBuilder();
		sb.append("select * from ")
			.append(getTableName())
			.append(" where status = 0 ");
		if(condition != null) {
			if(StringUtils.isNotEmpty(condition.getQuery())) {
				sb.append(" and title like '%").append(SQLUtils.escapeSqlLike(condition.getQuery())).append("%' ");
			}
			if(condition.getCid() != null && condition.getCid() != 0) {
				sb.append(" and category = ").append(condition.getCid());
			}
			if(condition.getMallid() != null && condition.getMallid() != 0) {
				sb.append(" and mallid = ").append(condition.getMallid());
			}
			if(condition.getCityid() != null && condition.getCityid() != 0) {
				sb.append(" and cityid = ").append(condition.getCityid());
			}
		}
		if(page != null) {
			if("starNum".equalsIgnoreCase(page.getOrderBy())) {
				sb.append(" order by starNum desc, updatetime desc").append(" limit ")
				.append(page.getStart() - 1).append(',')
				.append(page.getNum());
			} else if("location".equalsIgnoreCase(page.getOrderBy())) {
				sb.append(" order by updatetime desc").append(" limit ")
				.append(page.getStart() - 1).append(',')
				.append(page.getNum());
			} else {
				sb.append(" order by updatetime desc ").append(" limit ")
				.append(page.getStart() - 1).append(',')
				.append(page.getNum());
			}
		}
		String sql = sb.toString();
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Spot>(Spot.class));
	}
	
	public int count(QueryCondition condition) {
		StringBuilder sb = new StringBuilder();
		sb.append("select count(*) from ")
			.append(getTableName())
			.append(" where status = 0 ");
		if(condition != null) {
			if(StringUtils.isNotEmpty(condition.getQuery())) {
				sb.append(" and title like '%").append(SQLUtils.escapeSqlLike(condition.getQuery())).append("%' ");
			}
			if(condition.getCid() != null && condition.getCid() != 0) {
				sb.append(" and category = ").append(condition.getCid());
			}
			if(condition.getMallid() != null && condition.getMallid() != 0) {
				sb.append(" and mallid = ").append(condition.getMallid());
			}
			if(condition.getCityid() != null) {
				sb.append(" and cityid = ").append(condition.getCityid());
			}
		}
		String sql = sb.toString();
		return jdbcTemplate.queryForInt(sql);
	}
	
	public List<Spot> findAll() {
        String sql = "select * from #table# where status <> 2 order by addtime desc";
        return super.findBySql(sql);
    }
    
    public List<Spot> findByMallid(long mallid) {
        String sql = "select * from #table# where mallid = ? and status <> 2 order by addtime desc";
        return super.findBySql(sql, mallid);
    }
    
    public List<Spot> findByShopid(long shopid) {
        String sql = "select * from #table# where shopid = ? and status <> 2 order by addtime desc";
        return super.findBySql(sql, shopid);
    }
	
	public List<Spot> findByUserid(long userid) {
		String sql = "select * from #table# where userid = ? and status <> 2 order by addtime desc";
		return super.findBySql(sql, userid);
	}
	
	public Spot findByUser(long userid, long id) {
		String sql = "select * from #table# where userid = ? and id = ? and status <> 2";
		List<Spot> ret = super.findBySql(sql, userid, id);
		if (CollectionUtils.isEmpty(ret)) {
			return null;
		}
		return ret.get(0);
	}
	
	public int deleteById(long id) {
        StringBuilder sb = new StringBuilder();
        sb.append("update ")
            .append(getTableName())
            .append(" set status = 2 ")
            .append(" where id = ? ");
        String sql = sb.toString();
        return jdbcTemplate.update(sql, id);
    }
	
	public int deleteByUser(long id, long userid) {
		StringBuilder sb = new StringBuilder();
		sb.append("update ")
			.append(getTableName())
			.append(" set status = 2 ")
			.append(" where id = ? and userid = ?");
		String sql = sb.toString();
		return jdbcTemplate.update(sql, id, userid);
	}
	
	/**
	 * 必须本人，并且在优惠生效之前才能删除
	 * @param id
	 * @param userid
	 * @param timestamp
	 * @return
	 */
	public int deleteByUser(long id, long userid, long timestamp) {
		StringBuilder sb = new StringBuilder();
		sb.append("update ")
			.append(getTableName())
			.append(" set status = 2 ")
			.append(" where id = ? and userid = ? and starttime > ?");
		String sql = sb.toString();
		return jdbcTemplate.update(sql, id, userid, timestamp);
	}
}
